import random

import xlrd
import xlwt


# workbook = xlrd.open_workbook('成绩表.xlsx')
# 获取所有sheet名字
# print(workbook.sheet_names())

# 根据索引获取sheet名字
# print(workbook.sheet_by_index(0).name)
# print(workbook.sheet_by_name('2班').name)


# 获取所有的sheet对象
# sheets = workbook.sheets()
# for i in sheets:
#     print(i.name)


# 获取指定sheet的行数和列数
# sheet = workbook.sheet_by_index(0)
# print(sheet.nrows,sheet.ncols)



# cell
# sheet = workbook.sheet_by_index(0)
# cell = sheet.cell(1,1)
# print(cell.value)

# 获取指定行的某几列的cell对象
# cell = sheet.row_slice(1,1,4)
# for i in cell:
#     print(i.value)

# 获取指定列的某几列的cell对象
# cell = sheet.col_slice(0,1,sheet.nrows)
# for i in cell:
#     print(i.value)

# 获取指定行和列的值
# cell = sheet.cell_value(0,1)
# print(cell)

# 获取指定列的某几行的值
# cell = sheet.col_values(1,1,sheet.nrows)
# print(cell)

# 获取指定行的某几列的值
# cell = sheet.row_values(1,1,sheet.ncols)
# print(cell)


# cell类型
# sheet = workbook.sheet_by_index(0)
# ret = sheet.cell(0,0)
# print(ret.ctype) # 1 文本类型

# ret = sheet.cell(1,1)
# print(ret.ctype) # 2 number类型

# ret = sheet.cell(19,0)
# print(ret.ctype) # 3 日期类型

# ret = sheet.cell(19,0)
# print(ret.ctype) # 4 Boolean类型

# ret = sheet.cell(1,1)
# print(ret.ctype) # 0 empty类型



# 写入数据
# workbooks = xlwt.Workbook()
# sheet = workbooks.add_sheet('sheet1')
# headers = ['姓名','语文','英语','数学']
# for index,data in enumerate(headers):
#     sheet.write(0,index,data)
#
# name = ['张三','李四','王五']
# for index,name in enumerate(name):
#     sheet.write(index+1,0,name)
#
# for row in range(1,4):
#     for col in range(1,4):
#         sheet.write(row,col,random.randint(1,100))
#
# workbooks.save('成绩.xls')



# 编辑数据
rwd = xlrd.open_workbook('成绩表.xlsx')
rsheet = rwd.sheet_by_index(0)

# 总分
rsheet.put_cell(0,4,xlrd.XL_CELL_TEXT,'总分',None)
for row in range(1,rsheet.nrows):
    grades = rsheet.row_values(row,1,4)
    total = sum(grades)
    rsheet.put_cell(row,4,xlrd.XL_CELL_NUMBER,total,None)

# 平均分
nrows = rsheet.nrows
ncols = rsheet.ncols
for col in range(1,5):
    grades = rsheet.col_values(col,1,nrows)
    avg = sum(grades)/len(grades)
    rsheet.put_cell(nrows, col, xlrd.XL_CELL_NUMBER, avg, None)

wwb = xlwt.Workbook()
wsheet = wwb.add_sheet('sheet2')
for row in range(0,rsheet.nrows):
    for col in range(0,rsheet.ncols):
        wsheet.write(row,col,rsheet.cell_value(row,col))

wwb.save('abc.xls')























